/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package camada.bancodedados.daoimplemente.empresa;

import camada.bancodedados.conexao.Conexao;
import camada.negocio.dao.empresa.EmpresaDAO;
import eloma.classe.empresa.Empresa;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Klinger
 */
public class EmpresaDAOImpl implements EmpresaDAO {

    private static Connection con;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    
    @Override
    public void inserir(Empresa d) throws Exception {
        try {
            
            con = Conexao.getCon();
            con.setAutoCommit(false);

            final String SQL_SCRIPT = "INSERT INTO empresa(nome,nomeFantasia,endereco,numero,complemento,bairro,idMunicipio,estado,cep,insc_estadual,"
                                      + "insc_municipal,insc_federal,telefone,telefax,celular,email)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            
            stmt = con.prepareStatement(SQL_SCRIPT);
            
            stmt.setString(1, d.getNome());
            stmt.setString(2, d.getNomefantasia());
            stmt.setString(3, d.getEndereco());
            stmt.setInt(4, d.getNumero());
            stmt.setString(5, d.getComplemento());
            stmt.setString(6, d.getBairro());
            stmt.setInt(7, d.getMunicipio().getIdCidade());
            stmt.setString(8, d.getEstado());
            stmt.setString(9, d.getCep());
            stmt.setString(10, d.getInscricaoestadual());
            stmt.setString(11, d.getInscricaomunicipal());
            stmt.setString(12, d.getInscricaofederal());
            stmt.setString(13, d.getTelefone());
            stmt.setString(14, d.getTeleFax());
            stmt.setString(15, d.getCelular());
            stmt.setString(16, d.getEmail());

            stmt.executeUpdate();
            
             con.commit();

        } catch (SQLException e) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }
    }

    
    @Override
    public void atualizar(Empresa d) throws Exception {
        try {
            
            con = Conexao.getCon();
            con.setAutoCommit(false);
            
            final String SQL_SCRIPT
                    = "UPDATE EMPRESA SET nome = ?,nomefantasia = ?,inscricaofederal = ?,inscricaoestadual = ?,inscricaomunicipal = ?,endereco = ?,cidade = ?,cep = ?,bairro = ?, uf = ? where idEmpresa = ? ";
           
            stmt = con.prepareStatement(SQL_SCRIPT);
            
            stmt.setString(1, d.getNome());
            stmt.setString(2, d.getNomefantasia());
            stmt.setString(3, d.getEndereco());
            stmt.setInt(4, d.getNumero());
            stmt.setString(5, d.getComplemento());
            stmt.setString(6, d.getBairro());
            stmt.setInt(7, d.getMunicipio().getIdCidade());
            stmt.setString(8, d.getEstado());
            stmt.setString(9, d.getCep());
            stmt.setString(10, d.getInscricaoestadual());
            stmt.setString(11, d.getInscricaomunicipal());
            stmt.setString(12, d.getInscricaofederal());
            stmt.setString(13, d.getTelefone());
            stmt.setString(14, d.getTeleFax());
            stmt.setString(15, d.getCelular());
            stmt.setString(16, d.getEmail());
            stmt.setInt(17, d.getIdEmpresa());
            
            stmt.executeUpdate();
            
            con.commit();

        } catch (SQLException e) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }
    }
    
    @Override
    public void remover(Empresa empresa) throws Exception {
        try {
          
            con = Conexao.getCon();
            con.setAutoCommit(false);
            
            final String SQL_SCRIPT = "delete from empresa where idEmpresa = ?";
            
            stmt = con.prepareStatement(SQL_SCRIPT);
            
            stmt.setInt(1, empresa.getIdEmpresa());
            
            stmt.executeUpdate();
        
          con.commit();

        } catch (SQLException e) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }
    }
    
    
    @Override
    public List<Empresa> buscaTodos() throws Exception {
         try {

            con = Conexao.getCon();
            con.setAutoCommit(false);

            final String SQL_SCRIPT = "Select * from empresa order by idEmpresa";
            
            stmt = con.prepareStatement(SQL_SCRIPT);
            
            rs = stmt.executeQuery();
            
            con.commit();

        } catch (SQLException sqle) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }

        return carregarMultiplosResultados(rs);
    }
    
    private Empresa carregarSimpleResultado(ResultSet rs) throws Exception {
        if (rs.next()) {
            Empresa dto = new Empresa();
            carregarVO(dto, rs);
            return dto;
        } else {
            return null;
        }
    }

    public List<Empresa> carregarMultiplosResultados(ResultSet rs) throws Exception {
        List<Empresa> resultList = new ArrayList<Empresa>();
        while (rs.next()) {
            Empresa dto = new Empresa();
            carregarVO(dto, rs);
            resultList.add(dto);
        }
        return resultList;
    }

    private void carregarVO(Empresa dto, ResultSet rs) throws SQLException {
        try {

            dto.setIdEmpresa(rs.getInt("IdEmpresa"));
            dto.setNome(rs.getString("nome"));
            dto.setNomefantasia(rs.getString("nomefantasia"));
            dto.setInscricaoestadual(rs.getString("inscricaoestadual"));
            dto.setInscricaofederal(rs.getString("inscricaofederal"));
            dto.setInscricaomunicipal(rs.getString("inscricaomunicipal"));
            dto.setEndereco(rs.getString("endereco"));

            dto.setCep(rs.getString("cep"));
            dto.setBairro(rs.getString("bairro"));
            dto.setEstado(rs.getString("estado"));
            
        } catch (Exception e) {

        }
    }

}
